package com.xiyu.service.repository.customer.contact;

import com.xiyu.service.vo.customer.customerContact.CustomerContactPageQueryMailInput;
import com.xiyu.service.model.customer.contact.*;
import com.xiyu.service.model.customer.info.CustomerInfoFetcher;
import org.babyfish.jimmer.spring.repository.JRepository;
import org.babyfish.jimmer.Page;
import org.babyfish.jimmer.sql.ast.Predicate;
import org.babyfish.jimmer.sql.ast.tuple.Tuple3;

import java.util.List;

public interface CustomerContactMailRepository extends JRepository<CustomerContactMail, Long> {
    CustomerContactMailTable customerContactMailTable = CustomerContactMailTable.$;

    default void deleteByCustomerIds(List<Long> customerIds){
        sql().createDelete(customerContactMailTable)
                .where(customerContactMailTable.contact().customerId().in(customerIds))
                .execute();
    }

    default void deleteByExcludeId(Long contactId, List<Long> ids){
        sql().createDelete(customerContactMailTable)
                .where(customerContactMailTable.contactId().eq(contactId))
                .where(customerContactMailTable.id().notIn(ids))
                .execute();
    }

    default List<CustomerContactMail> getMailInfoByCustomerIds(List<Long> customerIds){
        return sql().createQuery(customerContactMailTable)
                .where(customerContactMailTable.contact().customer().id().in(customerIds))
                .orderBy(customerContactMailTable.contact().customerId().desc())
                .orderBy(customerContactMailTable.contact().isMain().desc())
                .orderBy(customerContactMailTable.contact().id().desc())
                .select(customerContactMailTable.fetch(CustomerContactMailFetcher.$.allTableFields()
                        .contact(CustomerContactFetcher.$.name().position().isMain().workState()
                                .customer(CustomerInfoFetcher.$.name()))
                ))
                .execute();
    }

    default List<Tuple3<String, String, Long>> publicListByMulti(String multiOption){
        return sql().createQuery(customerContactMailTable)
                .where(Predicate.or(customerContactMailTable.mailAddress().like(multiOption),
                        customerContactMailTable.contact().name().like(multiOption))
                )
                .where(customerContactMailTable.contact().customer().isRecycle().eq(false))
                .where(customerContactMailTable.contact().customer().isFallPool().eq(true))
                .select(customerContactMailTable.contact().name(), customerContactMailTable.mailAddress(), customerContactMailTable.id())
                .execute();
    }

    default List<Tuple3<String, String, Long>> privateListByMulti(String multiOption, List<Long> ownerUserId){
        return sql().createQuery(customerContactMailTable)
                .where(Predicate.or(customerContactMailTable.mailAddress().like(multiOption),
                        customerContactMailTable.contact().name().like(multiOption))
                )
                .where(customerContactMailTable.contact().customer().ownerId().in(ownerUserId))
                .where(customerContactMailTable.contact().customer().isRecycle().eq(false))
                .where(customerContactMailTable.contact().customer().isFallPool().eq(false))
                .select(customerContactMailTable.contact().name(), customerContactMailTable.mailAddress(), customerContactMailTable.id())
                .execute();
    }

    default List<Tuple3<String, String, Long>> publicListByAddress(String address, String name){
        return sql().createQuery(customerContactMailTable)
                .where(customerContactMailTable.mailAddress().eq(address))
                .where(customerContactMailTable.contact().name().eqIf(name))
                .where(customerContactMailTable.contact().customer().isRecycle().eq(false))
                .where(customerContactMailTable.contact().customer().isFallPool().eq(true))
                .select(customerContactMailTable.contact().name(), customerContactMailTable.mailAddress(), customerContactMailTable.id())
                .execute();
    }

    default List<Tuple3<String, String, Long>> privateListByAddress(String address, String name, List<Long> ownerUserId){
        return sql().createQuery(customerContactMailTable)
                .where(customerContactMailTable.mailAddress().eq(address))
                .where(customerContactMailTable.contact().name().eqIf(name))
                .where(customerContactMailTable.contact().customer().ownerId().in(ownerUserId))
                .where(customerContactMailTable.contact().customer().isRecycle().eq(false))
                .where(customerContactMailTable.contact().customer().isFallPool().eq(false))
                .select(customerContactMailTable.contact().name(), customerContactMailTable.mailAddress(), customerContactMailTable.id())
                .execute();
    }

    default Page<CustomerContactMail> pageQueryMail(CustomerContactPageQueryMailInput inputVO, List<Long> ownerIds){
        return sql().createQuery(customerContactMailTable)
                .where(customerContactMailTable.contact().customer().name().likeIf(inputVO.getName()))
                .where(customerContactMailTable.contact().customer().source().eqIf(inputVO.getSource()))
                .where(customerContactMailTable.contact().customer().grade().eqIf(inputVO.getGrade()))
                .where(customerContactMailTable.contact().customer().stage().eqIf(inputVO.getStage()))
                .where(customerContactMailTable.contact().customer().state().eqIf(inputVO.getState()))
                .where(customerContactMailTable.contact().customer().ownerId().eqIf(inputVO.getOwnerId()))
                .where(customerContactMailTable.contact().customer().isRecycle().eq(false))
                .whereIf(inputVO.getCustomerType() == 1,Predicate.and( customerContactMailTable.contact().customer().isFallPool().eq(false), customerContactMailTable.contact().customer().ownerId().in(ownerIds)))
                .whereIf(inputVO.getCustomerType() == 2, customerContactMailTable.contact().customer().isFallPool().eq(true))
                .orderBy(customerContactMailTable.id().desc())
                .select(customerContactMailTable.fetch(CustomerContactMailFetcher.$.allTableFields()
                        .contact(CustomerContactFetcher.$.allTableFields()
                                .customer(CustomerInfoFetcher.$.allTableFields())
                        )
                ))
                .fetchPage(inputVO.getPageNo() - 1, inputVO.getPageSize());
    }

    List<CustomerContactMail> findByContactId(Long contactId);


}